package com.pentofab.payrollmanagement.database.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;
import com.pentofab.payrollmanagement.dao.model.EsiPfBonus;
import com.pentofab.payrollmanagement.dao.model.PTSlab;
import com.pentofab.payrollmanagement.dao.model.SalaryAdvance;
import com.pentofab.payrollmanagement.dao.model.SalaryProfile;
import com.pentofab.payrollmanagement.dao.model.SalaryStracture;
import com.pentofab.payrollmanagement.database.SalaryRepository;
import com.pentofab.payrollmanagement.database.impl.SalaryTableSQLConstants.TABLE_PT_SLAB;
import com.pentofab.payrollmanagement.database.impl.SalaryTableSQLConstants.TABLE_SALARY_STRACTUR;
import com.pentofab.payrollmanagement.database.impl.SalaryTableSQLConstants.Table_SALARY_ADVANCE;
import com.pentofab.payrollmanagement.database.impl.SalaryTableSQLConstants.Table_SALARY_PROFILE;
import com.pentofab.payrollmanagement.exception.BusinessException;
import com.pentofab.payrollmanagement.utilities.PayRollUtility;

public class SalaryRepositoryimpl implements SalaryRepository {

	Connection conn = null;

	public SalaryRepositoryimpl() {

		conn = CreateDBConnection.cteateConnectionObj();
	}
	
	@Override
	public ArrayList<SalaryProfile> getSalaryProfiles()
			throws Exception {
		
		PreparedStatement preparedStatement;
		ArrayList<SalaryProfile> salaryProfileList = new ArrayList<SalaryProfile>();
		try {
			preparedStatement = conn
					.prepareStatement(SalaryTableSQLConstants.selectSalaryProfile);

			ResultSet rs = preparedStatement.executeQuery();
			PayRollUtility.debug("preparedStatement getSalaryProfiles : "+preparedStatement); 
			if (!rs.isBeforeFirst()) {
				return salaryProfileList;
			}

			while (rs.next()) {

				SalaryProfile srp = new SalaryProfile();

				srp.setBasic(rs.getString(Table_SALARY_PROFILE.Basic));
				srp.setBonus(rs.getString(Table_SALARY_PROFILE.Bonus));
				srp.setConveyance(rs.getString(Table_SALARY_PROFILE.Convy));
				srp.setDesc(rs.getString(Table_SALARY_PROFILE.description));
				srp.setEsi(rs.getString(Table_SALARY_PROFILE.ESI));
				srp.setHra(rs.getString(Table_SALARY_PROFILE.HRA));
				srp.setMaxPF(rs.getString(Table_SALARY_PROFILE.maxPF));
				srp.setMedicalAllo(rs
						.getString(Table_SALARY_PROFILE.MedicalAllow));
				srp.setPfPer(rs.getString(Table_SALARY_PROFILE.pfPercentage));
				srp.setProfileName(rs.getString(Table_SALARY_PROFILE.ProfileId));
				srp.setSpecialAllow(rs
						.getString(Table_SALARY_PROFILE.SpecialAllow));
				srp.setOthers(rs
						.getString(Table_SALARY_PROFILE.others));

				salaryProfileList.add(srp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new BusinessException(e,"Error while getting Salary profile details");
		}
		return salaryProfileList;
	}

	@Override
	public void addSalaryProfile(SalaryProfile salaryProfile)
			throws Exception {
		
		
		try{
			PreparedStatement preparedStatement = conn
					.prepareStatement(SalaryTableSQLConstants.insertIntoSalaryProfile);
			
			preparedStatement.setString(1, salaryProfile.getDesc());
			preparedStatement.setString(2, salaryProfile.getProfileName());
			preparedStatement.setDouble(3, Double.parseDouble(salaryProfile.getHra()));
			preparedStatement.setDouble(4, Double.parseDouble(salaryProfile.getBasic()));
			preparedStatement.setDouble(5, Double.parseDouble((salaryProfile.getConveyance() == null || 
					salaryProfile.getConveyance().trim().equals("") ) ? "0.0" : salaryProfile.getConveyance()));
			
			preparedStatement.setDouble(6, Double.parseDouble((salaryProfile.getMedicalAllo() == null || 
					salaryProfile.getMedicalAllo().trim().equals("") ) ? "0.0" : salaryProfile.getMedicalAllo()));
			
			preparedStatement.setDouble(7, Double.parseDouble((salaryProfile.getSpecialAllow() == null || 
					salaryProfile.getSpecialAllow().trim().equals("") ) ? "0.0" : salaryProfile.getSpecialAllow()));
			
			preparedStatement.setDouble(8, Double.parseDouble((salaryProfile.getMaxPF() == null || 
					salaryProfile.getMaxPF().trim().equals("") ) ? "0.0" : salaryProfile.getMaxPF()));
			
			preparedStatement.setDouble(9, Double.parseDouble((salaryProfile.getEsi() == null || 
					salaryProfile.getEsi().trim().equals("") ) ? "0.0" : salaryProfile.getEsi()));
			
			preparedStatement.setDouble(10, Double.parseDouble((salaryProfile.getPfPer() == null || 
					salaryProfile.getPfPer().trim().equals("") ) ? "0.0" : salaryProfile.getPfPer()));
			
			preparedStatement.setDouble(11, Double.parseDouble((salaryProfile.getBonus() == null || 
					salaryProfile.getBonus().trim().equals("") ) ? "0.0" : salaryProfile.getBonus()));
			preparedStatement.setDouble(12, Double.parseDouble((salaryProfile.getOthers() == null || 
					salaryProfile.getBonus().trim().equals("") ) ? "0.0" : salaryProfile.getOthers()));
			
			
			PayRollUtility.debug("preparedStatement addSalaryProfile : "+preparedStatement); 
			preparedStatement.executeUpdate();
		
		}catch(MySQLIntegrityConstraintViolationException exception){

			throw new Exception("Profile with id already exists");
		}catch (Exception e){
			e.printStackTrace();
			throw new Exception("Error while adding user profile");
		}
	}

	@Override
	public void modifySalaryProfile(SalaryProfile salaryProfile)
			throws Exception {
		
		try{
			
			PreparedStatement preparedStatement = conn
					.prepareStatement(SalaryTableSQLConstants.updateSalaryProfile);
			
			preparedStatement.setString(1, salaryProfile.getDesc());
			preparedStatement.setDouble(2, Double.parseDouble(salaryProfile.getHra()));
			preparedStatement.setDouble(3, Double.parseDouble(salaryProfile.getBasic()));
			preparedStatement.setDouble(4, Double.parseDouble((salaryProfile.getConveyance() == null || 
					salaryProfile.getConveyance().trim().equals("") ) ? "0.0" : salaryProfile.getConveyance()));
			
			preparedStatement.setDouble(5, Double.parseDouble((salaryProfile.getMedicalAllo() == null || 
					salaryProfile.getMedicalAllo().trim().equals("") ) ? "0.0" : salaryProfile.getMedicalAllo()));
			
			preparedStatement.setDouble(6, Double.parseDouble((salaryProfile.getSpecialAllow() == null || 
					salaryProfile.getSpecialAllow().trim().equals("") ) ? "0.0" : salaryProfile.getSpecialAllow()));
			
			preparedStatement.setDouble(7, Double.parseDouble((salaryProfile.getMaxPF() == null || 
					salaryProfile.getMaxPF().trim().equals("") ) ? "0.0" : salaryProfile.getMaxPF()));
			
			preparedStatement.setDouble(8, Double.parseDouble((salaryProfile.getEsi() == null || 
					salaryProfile.getEsi().trim().equals("") ) ? "0.0" : salaryProfile.getEsi()));
			
			preparedStatement.setDouble(9, Double.parseDouble((salaryProfile.getPfPer() == null || 
					salaryProfile.getPfPer().trim().equals("") ) ? "0.0" : salaryProfile.getPfPer()));
			
			preparedStatement.setDouble(10, Double.parseDouble((salaryProfile.getBonus() == null || 
					salaryProfile.getBonus().trim().equals("") ) ? "0.0" : salaryProfile.getBonus()));
			
			preparedStatement.setDouble(11, Double.parseDouble((salaryProfile.getOthers() == null || 
					salaryProfile.getBonus().trim().equals("") ) ? "0.0" : salaryProfile.getOthers()));
			
			preparedStatement.setString(12, salaryProfile.getProfileName());
			
			PayRollUtility.debug("preparedStatement modifySalaryProfile : "+preparedStatement); 
			preparedStatement.executeUpdate();
		
			preparedStatement = conn.prepareStatement(SalaryTableSQLConstants.updateSalaryStractureDuringProfileModify);
			
			preparedStatement.setDouble(1, Double.parseDouble(salaryProfile.getBasic()));
			preparedStatement.setDouble(2, Double.parseDouble(salaryProfile.getHra()));
			preparedStatement.setDouble(3, Double.parseDouble((salaryProfile.getConveyance() == null || 
					salaryProfile.getConveyance().trim().equals("") ) ? "0.0" : salaryProfile.getConveyance()));
	
			preparedStatement.setDouble(4, Double.parseDouble((salaryProfile.getSpecialAllow() == null || 
					salaryProfile.getSpecialAllow().trim().equals("") ) ? "0.0" : salaryProfile.getSpecialAllow()));
			
			preparedStatement.setDouble(5, Double.parseDouble((salaryProfile.getMedicalAllo() == null || 
					salaryProfile.getMedicalAllo().trim().equals("") ) ? "0.0" : salaryProfile.getMedicalAllo()));
			
			
			preparedStatement.setDouble(6, Double.parseDouble((salaryProfile.getMaxPF() == null || 
					salaryProfile.getMaxPF().trim().equals("") ) ? "0.0" : salaryProfile.getMaxPF()));
			
			preparedStatement.setDouble(7, Double.parseDouble((salaryProfile.getEsi() == null || 
					salaryProfile.getEsi().trim().equals("") ) ? "0.0" : salaryProfile.getEsi()));
			
			preparedStatement.setDouble(8, Double.parseDouble((salaryProfile.getBonus() == null || 
					salaryProfile.getBonus().trim().equals("") ) ? "0.0" : salaryProfile.getBonus()));
			
			preparedStatement.setDouble(9, Double.parseDouble((salaryProfile.getPfPer() == null || 
					salaryProfile.getPfPer().trim().equals("") ) ? "0.0" : salaryProfile.getPfPer()));
			
			preparedStatement.setDouble(10, Double.parseDouble((salaryProfile.getOthers() == null || 
					salaryProfile.getBonus().trim().equals("") ) ? "0.0" : salaryProfile.getOthers()));
			
			preparedStatement.setString(11, salaryProfile.getProfileName());
			PayRollUtility.debug("preparedStatement updateSalaryStractureDuringProfileModify : "+preparedStatement); 
			preparedStatement.executeUpdate();
			
		}catch (Exception e){

			throw new Exception("Error while adding salary profile");
		}
	}

	@Override
	public void deleteSalaryProfile(String profieName) throws Exception {
		
		PreparedStatement preparedStatement  = null;
			
			 preparedStatement = conn
						.prepareStatement(SalaryTableSQLConstants.getEMPIDFORSALARYPROFLE);
			 PayRollUtility.debug("preparedStatement deleteSalaryProfile check association  : "+preparedStatement); 
			 
			 ResultSet rs = preparedStatement.executeQuery();
			
			 
			 if(rs.isBeforeFirst()){
				 
				StringBuffer eid = new StringBuffer();
				while(rs.next()){
					eid.append(rs.getString(1));
					eid.append(",");
				}
				
				throw new BusinessException("Salary profile "+profieName+" is associated with following employees "+eid+" please "
						+ "asign different profile to employees before delation.");
			 }
		try {
			
			preparedStatement = conn.prepareStatement(SalaryTableSQLConstants.deleteSalaryProfile);
			preparedStatement.setString(1, profieName);
			
			PayRollUtility.debug("preparedStatement deleteSalaryProfile : "+preparedStatement); 
			preparedStatement.executeUpdate();

		}catch (Exception e){

			throw new Exception("Error while deleting salary profile");
		}
	}

	@Override
	public ArrayList<SalaryAdvance> getSalaryAdvance(String empId)
			throws Exception {
		
		PreparedStatement preparedStatement;
		ArrayList<SalaryAdvance> salaryAdvanceList = new ArrayList<SalaryAdvance>();
		try {
			preparedStatement = conn.prepareStatement((empId == null || empId.trim().equals("")) ? SalaryTableSQLConstants.selectSalaryadvance : 
							SalaryTableSQLConstants.selectSalaryadvanceByemployeeid);
			if(empId != null && !empId.equals("")){
				preparedStatement.setString(1, empId);
			}
			
			PayRollUtility.debug("preparedStatement selectSalaryadvanceByemployeeid : "+preparedStatement);
			ResultSet rs = preparedStatement.executeQuery();
			if (!rs.isBeforeFirst()) {
				return salaryAdvanceList;
			}

			while (rs.next()) {

				SalaryAdvance sAdv = new SalaryAdvance();

				sAdv.setEmployeeId(rs.getString(Table_SALARY_ADVANCE.employeeid));
				sAdv.setAdvance(rs.getString(Table_SALARY_ADVANCE.advance));
				sAdv.setNoOfEMI(rs.getString(Table_SALARY_ADVANCE.noofEMI));
				sAdv.setStartDate(rs.getString(Table_SALARY_ADVANCE.startDate));
				sAdv.setBalance(rs.getString(Table_SALARY_ADVANCE.Balance));
				sAdv.setRownum(rs.getInt(Table_SALARY_ADVANCE.rownum));
				
				/*DecimalFormat df = new DecimalFormat("#.##");
				Double emiAmount = Double.parseDouble(sAdv.getAdvance())/Double.parseDouble(sAdv.getNoOfEMI());
				emiAmount = Math.ceil(emiAmount);
				String oneEMi = df.format(emiAmount);
				
				String startDate = sAdv.getStartDate();
				Date start = PayRollUtility.convertStringToDate(startDate);
				Calendar startCal = Calendar.getInstance();
				startCal.setTime(start);
				Calendar cureCalendar = Calendar.getInstance();*/
				
				/*double balance =  Double.parseDouble(sAdv.getAdvance()) - (Double.parseDouble(oneEMi) * (monthsBetween ) );
				sAdv.setBalance(String.valueOf(balance));*/
				salaryAdvanceList.add(sAdv);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new BusinessException(e,"Error while getting Salary advance details");
		}
		return salaryAdvanceList;
	
		
	}

	@Override
	public void appliForAdvance(SalaryAdvance salaryAdvance) throws Exception {
		
		try{
			PreparedStatement preparedStatement = conn
					.prepareStatement(SalaryTableSQLConstants.insertIntoSalaryadvance);
			
			preparedStatement.setString(1, salaryAdvance.getEmployeeId());
			preparedStatement.setString(2, salaryAdvance.getAdvance());
			preparedStatement.setString(3,"99");
			preparedStatement.setString(4, PayRollUtility.convertDateToString(new Date()));
			preparedStatement.setString(5, salaryAdvance.getAdvance());
			
			
			PayRollUtility.debug("preparedStatement addSalaryadvance : "+preparedStatement); 
			preparedStatement.executeUpdate();
		
		}catch(MySQLIntegrityConstraintViolationException exception){

			throw new Exception("Salary advance already created for employee");
		}catch (Exception e){

			throw new Exception("Error while adding slary advance for empoyee ; "+salaryAdvance.getEmployeeId());
		}
	
		
		
	}

	@Override
	public void deleteSalaryAdvance(String empId, int rowNum)
			throws Exception {
			PreparedStatement preparedStatement=conn.prepareStatement(SalaryTableSQLConstants.deleteSalaryadvance);
			preparedStatement.setString(1, empId);
			preparedStatement.setInt(2, rowNum);
			PayRollUtility.debug("preparedStatement deleteSalaryadvance : "+preparedStatement); 
			preparedStatement.executeUpdate();
		
	}

	@Override
	public ArrayList<PTSlab> getPTSlab() throws BusinessException {
		
		ArrayList<PTSlab> ptSlabList = null;
		try{
			PreparedStatement preparedStatement=conn.prepareStatement(SalaryTableSQLConstants.getPTSlab);
			PayRollUtility.debug(" preparedStatement getPTSlab : "+preparedStatement);
			
			ResultSet rs = preparedStatement.executeQuery();
			
			if(!rs.isBeforeFirst()){
				return null;
			}
			
			ptSlabList = new ArrayList<PTSlab>();
			
			while(rs.next()){
				
				PTSlab ptSlab = new PTSlab();
				
				ptSlab.setMaxRange(rs.getString(TABLE_PT_SLAB.maxRange));
				ptSlab.setPtAmount(rs.getString(TABLE_PT_SLAB.ptAmount));
				ptSlabList.add(ptSlab);
			}
		
		}catch(Exception e){
			
			throw new BusinessException(e,"Error while getting Profeccsional tax Slab");
			
		}
		return ptSlabList;
	}

	@Override
	public void addPTSlab(String ptTaxAmount, String maxLimit)
			throws Exception {
		try{
			PreparedStatement preparedStatement = conn
					.prepareStatement(SalaryTableSQLConstants.addPTSlab);
			
			preparedStatement.setString(1, ptTaxAmount);
			preparedStatement.setString(2,maxLimit);
			
			PayRollUtility.debug("preparedStatement addPTSlab : "+preparedStatement); 
			preparedStatement.executeUpdate();
		
		}catch(MySQLIntegrityConstraintViolationException exception){

			throw new Exception("Professional tax slab already defined for "+maxLimit);
		}catch (Exception e){

			throw new Exception("Error while adding Professional tax slab for max limit  "+maxLimit);
		}
		
	}
	@Override
	public void updatePTSlab(String ptTaxAmount, String maxLimit)
			throws Exception {
		try{
			PreparedStatement preparedStatement = conn
					.prepareStatement(SalaryTableSQLConstants.updatePTSlab);
			
			preparedStatement.setString(1, ptTaxAmount);
			preparedStatement.setString(2,maxLimit);
			preparedStatement.setString(3, ptTaxAmount);
			preparedStatement.setString(4,maxLimit);
			
			PayRollUtility.debug("preparedStatement updatePTSlab : "+preparedStatement); 
			preparedStatement.executeUpdate();
		
		}catch(MySQLIntegrityConstraintViolationException exception){

			throw new Exception("Professional tax slab already defined for "+maxLimit);
		}catch (Exception e){

			throw new Exception("Error while updating Professional tax slab for max limit  "+maxLimit);
		}
		
	}
	@Override
	public double getPT(String netPay) throws Exception{
		try{
			PreparedStatement preparedStatement = conn
					.prepareStatement(SalaryTableSQLConstants.getPtSlab);
			
			preparedStatement.setString(1, netPay);
			
			PayRollUtility.debug("preparedStatement getPT : "+preparedStatement); 
			ResultSet rs = preparedStatement.executeQuery();
		
			while(rs.next()){
				return rs.getDouble(1);
			}
			
		}catch (Exception e){

			throw new Exception("Error while updating Professional tax slab for net pay  "+netPay);
		}
		return 0;
	}
	@Override
	public void deletePTSlab(String maxAmount) throws Exception {
		
		try{

		PreparedStatement preparedStatement=conn.prepareStatement(SalaryTableSQLConstants.deletePTSlab);
		preparedStatement.setString(1, maxAmount);
		PayRollUtility.debug("preparedStatement deletePTSlab : "+preparedStatement); 
		preparedStatement.executeUpdate();
		}catch(Exception e){
			throw new BusinessException(e,"Error hiwl deleting PTSlab for maxrange :"+maxAmount);
		}

	}

	@Override
	public ArrayList<String> getAllEmpSalaryStracture() throws Exception {
		
		ArrayList<String> allEmpStrList = new ArrayList<String>();
		try{
			PreparedStatement preparedStatement=conn.prepareStatement(SalaryTableSQLConstants.getAllEmpSalaryStracture);
			PayRollUtility.debug(" preparedStatement getSalaryStractureForEmp : "+preparedStatement);
			
			ResultSet rs = preparedStatement.executeQuery();
			
			if(!rs.isBeforeFirst()){
				return allEmpStrList;
			}
			
			while(rs.next()){
				
				StringBuffer cur = new StringBuffer();
				
				for(int i=1;i<=27;i++){
					cur.append(rs.getString(i));
					if(i<27){
						cur.append(", ");
					}
				}
				allEmpStrList.add(cur.toString());
			}
		}catch(Exception e){
			
			throw new BusinessException(e,"Error while getting salary stracture for all employees");
		}
		return allEmpStrList;
	}

	@Override
	public SalaryStracture getSalaryStractureForEmp(String empId)
			throws Exception {
		SalaryStracture salaryStracture = null;
		try{
			PreparedStatement preparedStatement=conn.prepareStatement(SalaryTableSQLConstants.getSalaryStractureByEmp);
			preparedStatement.setString(1, empId);
			PayRollUtility.debug(" preparedStatement getSalaryStractureForEmp : "+preparedStatement);
			
			ResultSet rs = preparedStatement.executeQuery();
			
			if(!rs.isBeforeFirst()){
				return salaryStracture;
			}
			
			while(rs.next()){
				
				salaryStracture = new SalaryStracture();
				salaryStracture.setEmployeeID(rs.getString(TABLE_SALARY_STRACTUR.employeeID));
				salaryStracture.setGrossSalary(rs.getString(TABLE_SALARY_STRACTUR.grossSalary));
				salaryStracture.setBasic(rs.getString(TABLE_SALARY_STRACTUR.basic));
				salaryStracture.setHra(rs.getString(TABLE_SALARY_STRACTUR.HRA));
				salaryStracture.setConveyance(rs.getString(TABLE_SALARY_STRACTUR.conveyance));
				salaryStracture.setMedicalAllowance(rs.getString(TABLE_SALARY_STRACTUR.medicalAllowance));
				salaryStracture.setSpecialAllowance(rs.getString(TABLE_SALARY_STRACTUR.specialAllowance));
				salaryStracture.setOthers(rs.getString(TABLE_SALARY_STRACTUR.others));
				
				salaryStracture.setPfrequired(rs.getBoolean(TABLE_SALARY_STRACTUR.pfrequired));
				salaryStracture.setVoluntary(rs.getBoolean(TABLE_SALARY_STRACTUR.voluntary));
				salaryStracture.setExcludingBase(rs.getBoolean(TABLE_SALARY_STRACTUR.excludingBase));
				salaryStracture.setEmployeeCont(rs.getString(TABLE_SALARY_STRACTUR.employeeCont));
				salaryStracture.setEmployeerCont(rs.getString(TABLE_SALARY_STRACTUR.employeerCont));
				salaryStracture.setPfNumber(rs.getString(TABLE_SALARY_STRACTUR.pfnumber));
				
				salaryStracture.setEsirequired(rs.getBoolean(TABLE_SALARY_STRACTUR.esirequired));
				salaryStracture.setEsiNumber(rs.getString(TABLE_SALARY_STRACTUR.esinumber));
				
				salaryStracture.setEl(rs.getString(TABLE_SALARY_STRACTUR.el));
				salaryStracture.setCl(rs.getString(TABLE_SALARY_STRACTUR.cl));
				salaryStracture.setSl(rs.getString(TABLE_SALARY_STRACTUR.sl));
				salaryStracture.setBankCode(rs.getString(TABLE_SALARY_STRACTUR.bankCode));
				salaryStracture.setAccountNo(rs.getString(TABLE_SALARY_STRACTUR.accountNo));
				
			}
		}catch(Exception e){
			
			throw new BusinessException(e,"Error while getting salary stracture for employee : "+empId);
		}
		return salaryStracture;
	}

	@Override
	public void insertSalaryStracture(SalaryStracture salaryStracture)
			throws Exception {
		try{
			PreparedStatement preparedStatement = conn
					.prepareStatement(SalaryTableSQLConstants.insertIntoSalaryStracture);
			
			preparedStatement.setString(1, salaryStracture.getEmployeeID());
			preparedStatement.setString(2, salaryStracture.getGrossSalary());
			preparedStatement.setString(3, salaryStracture.getBasic());
			
			String hra =  salaryStracture.getHra();
			preparedStatement.setString(4, (hra==null || hra.trim().equals("")) ? "0" : hra);
			
			String conv =  salaryStracture.getConveyance();
			preparedStatement.setString(5, (conv==null || conv.trim().equals("")) ? "0" : conv);
			
			String splAllow = salaryStracture.getSpecialAllowance();
			preparedStatement.setString(6, (splAllow==null || splAllow.trim().equals("")) ? "0" : splAllow);
			
			String medicAllow = salaryStracture.getMedicalAllowance();
			preparedStatement.setString(7, (medicAllow==null || medicAllow.trim().equals("")) ? "0" : medicAllow);
			
			String others = salaryStracture.getMedicalAllowance();
			preparedStatement.setString(8, (others==null || others.trim().equals("")) ? "0" : others);

			preparedStatement.setBoolean(9, salaryStracture.isPfrequired());
			preparedStatement.setBoolean(10, salaryStracture.isVoluntary());
			preparedStatement.setBoolean(11, salaryStracture.isExcludingBase());
			
			String empCOnt =  salaryStracture.getEmployeeCont();
			preparedStatement.setString(12, (empCOnt==null || empCOnt.trim().equals("")) ? "0" : empCOnt);
			
			String emplrCont =  salaryStracture.getEmployeerCont();
			preparedStatement.setString(13, (emplrCont==null || emplrCont.trim().equals("")) ? "0" : emplrCont);
			preparedStatement.setString(14, salaryStracture.getPfNumber());
			
			preparedStatement.setBoolean(15, salaryStracture.isEsirequired());
			preparedStatement.setString(16, salaryStracture.getEsiNumber());
			
			preparedStatement.setString(17, salaryStracture.getEl());
			preparedStatement.setString(18, salaryStracture.getCl());
			preparedStatement.setString(19, salaryStracture.getSl());
			
			preparedStatement.setString(20, salaryStracture.getBankCode());
			preparedStatement.setString(21, salaryStracture.getAccountNo());
			
			PayRollUtility.debug("preparedStatement insertSalaryStracture : "+preparedStatement); 
			preparedStatement.executeUpdate();
		
		}catch(MySQLIntegrityConstraintViolationException exception){

			throw new Exception("Salary plan exists for employee["+salaryStracture.getEmployeeID()+"]. Plese updateit if equired.");
		}catch (Exception e){
			
			throw new BusinessException(e,"Error while ading salary stracture for for employee["+salaryStracture.getEmployeeID()+"].");
		}
		
	}

	@Override
	public void updateSalaryStracture(SalaryStracture salaryStracture)
			throws Exception {
		try{
			PreparedStatement preparedStatement = conn
					.prepareStatement(SalaryTableSQLConstants.updateSalaryStracture);
			
			
			preparedStatement.setString(1, salaryStracture.getGrossSalary());
			preparedStatement.setString(2, salaryStracture.getBasic());
			
			String hra =  salaryStracture.getHra();
			preparedStatement.setString(3, (hra==null || hra.trim().equals("")) ? "0" : hra);
			
			String conv =  salaryStracture.getConveyance();
			preparedStatement.setString(4, (conv==null || conv.trim().equals("")) ? "0" : conv);
			
			String splAllow = salaryStracture.getSpecialAllowance();
			preparedStatement.setString(5, (splAllow==null || splAllow.trim().equals("")) ? "0" : splAllow);
			
			String medicAllow = salaryStracture.getMedicalAllowance();
			preparedStatement.setString(6, (medicAllow==null || medicAllow.trim().equals("")) ? "0" : medicAllow);
			
			String others = salaryStracture.getMedicalAllowance();
			preparedStatement.setString(7, (others==null || others.trim().equals("")) ? "0" : others);

			preparedStatement.setBoolean(8, salaryStracture.isPfrequired());
			preparedStatement.setBoolean(9, salaryStracture.isVoluntary());
			preparedStatement.setBoolean(10, salaryStracture.isExcludingBase());
			
			String empCOnt =  salaryStracture.getEmployeeCont();
			preparedStatement.setString(11, (empCOnt==null || empCOnt.trim().equals("")) ? "0" : empCOnt);
			
			String emplrCont =  salaryStracture.getEmployeerCont();
			preparedStatement.setString(12, (emplrCont==null || emplrCont.trim().equals("")) ? "0" : emplrCont);
			preparedStatement.setString(13, salaryStracture.getPfNumber());
			
			preparedStatement.setBoolean(14, salaryStracture.isEsirequired());
			preparedStatement.setString(15, salaryStracture.getEsiNumber());
			
			preparedStatement.setString(16, salaryStracture.getEl());
			preparedStatement.setString(17, salaryStracture.getCl());
			preparedStatement.setString(18, salaryStracture.getSl());
			
			preparedStatement.setString(19, salaryStracture.getBankCode());
			preparedStatement.setString(20, salaryStracture.getAccountNo());
			
			preparedStatement.setString(21, salaryStracture.getEmployeeID());
			
			PayRollUtility.debug("preparedStatement updateSalaryStracture : "+preparedStatement); 
			preparedStatement.executeUpdate();
		
		}catch (Exception e){

			throw new BusinessException(e,"Error while modifying salary stracture for for employee["+salaryStracture.getEmployeeID()+"].");
		}
		
	}
	
	@Override
	public ArrayList<HashMap<String, String>> getSalaryReport(String employeeId) throws BusinessException{
		
		ArrayList<HashMap<String, String>> salaryInfoMapList = new ArrayList<HashMap<String,String>>();
		try{
			
			String query = (employeeId ==null) ? SalaryTableSQLConstants.getSalaryRport : SalaryTableSQLConstants.getSalaryRportForEMp;
			
			PreparedStatement preparedStatement = conn.prepareStatement(query);
			if((employeeId !=null)){
				preparedStatement.setString(1, employeeId);
			}
			System.out.println(preparedStatement);
			ResultSet rs = preparedStatement.executeQuery();
			
			String[] colnms = SalaryTableSQLConstants.salaryReportConms.split(",");
			
			while(rs.next()){
				
				HashMap<String, String>  infoMap= new HashMap<String, String>();
				
				for(int i = 0;i< colnms.length;i++){
					infoMap.put(colnms[i], PayRollUtility.decilamFormat(rs.getString(colnms[i])));
				}
				salaryInfoMapList.add(infoMap);
			}
		
		}catch (Exception e){

			throw new BusinessException(e,"Error while getting salary report");
		}
		
		return salaryInfoMapList;
		
	}
	

	public EsiPfBonus getEsiPfBonus() throws BusinessException{

		EsiPfBonus esiPfBonus= new EsiPfBonus();
		try{
			PreparedStatement preparedStatement = conn
					.prepareStatement(SalaryTableSQLConstants.getESIPFBonus);
			
			
			PayRollUtility.debug("preparedStatement getEsiPfBonus : "+preparedStatement); 
			ResultSet rs = preparedStatement.executeQuery();
		
			if(rs.next()){
				
				esiPfBonus.setEsi(rs.getDouble(1));
				esiPfBonus.setPf(rs.getDouble(2));
				esiPfBonus.setBonus(rs.getDouble(3));
			
			}
			
		}catch(Exception e){
			throw new BusinessException(e,e.getMessage());
		}
		return esiPfBonus;
	}
	
	public void setEsiPfBonus(EsiPfBonus esiPfBonus) throws BusinessException{

		try{
			
			PreparedStatement preparedStatement = conn
					.prepareStatement(SalaryTableSQLConstants.setESIPFBonus);
			preparedStatement.setDouble(1, esiPfBonus.getEsi());
			preparedStatement.setDouble(2, esiPfBonus.getPf());
			preparedStatement.setDouble(3, esiPfBonus.getBonus());
			preparedStatement.executeUpdate();

		}catch(Exception e){
			throw new BusinessException(e,"Error while adding ESI PF Bonus information.");
		}
	}

	public HashMap<String, String> getComputedSalary(String empId,String month,String year,String company,
			String dept,String unit,String design) throws BusinessException {

		try{
			
			String query = SalaryTableSQLConstants.getPaidSalaryInfo;
			if(empId != null){
				query = query+" and EMPID = ?";
			}
			if(company != null){
				query = query+" and Company = ?";
			}
			if(dept != null){
				query = query+" and Dept = ?";
			}
			if(unit != null){
				query = query+" and Unit = ?";
			}
			if(design != null){
				query = query+" and Design = ?";
			}
			PreparedStatement preparedStatement = conn.prepareStatement(query);
			preparedStatement.setString(1, month);
			preparedStatement.setString(2, year);
			
			if(empId != null){
				
				preparedStatement.setString(3, empId);
			}
			
			if(company != null){
				preparedStatement.setString(4, company);
			}
			if(dept != null){
				preparedStatement.setString(5, dept);
			}
			if(unit != null){
				preparedStatement.setString(6, unit);
			}
			if(design != null){
				
				preparedStatement.setString(7, design);
			}
			
			System.out.println("preparedStatement : "+preparedStatement);
			ResultSet rs = preparedStatement.executeQuery();
			SalaryStracture salaryStracture= new SalaryStracture();
			HashMap<String, String>  infoMap= new HashMap<String, String>();
			while(rs.next()){
				
				String currentEmp = rs.getString(1);
				StringBuffer data = new StringBuffer();
				for (int i = 1; i <= 25; i++) {
					data.append(rs.getString(i));
					if(i < 25){
						data.append(",");
					}
				}
				
				infoMap.put(currentEmp, data.toString());
				data = null;
			}
			
			return infoMap;

		}catch(Exception e){
			throw new BusinessException(e,"Error while adding ESI PF Bonus information.");
		}
	}
	
	public void insertOrUpdatePaidSalaryInfo(String empId, String month, String year,SalaryStracture salaryStracture) throws Exception{
		
		PayRollUtility.debug("Entered method insertOrUpdatePaidSalaryInfo for emp"+empId);
		
		/**
		 * String insetIntoPaidsalaryinfo = "insert into payroll.paidsalaryinfo(EMPID,MONTH,YEAR,NAME,Company,Dept,Unit,"
			+ "Design,ESINumber,PFNumber,MaxDays,PaidDays,Gross,Basic,HRA,Conveyance,Special,"
			+ "Medic,Others,INCENTIVE,PF,ESI,PTax,LOP,Earnings,DECUCATION,NetPay) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) "
			+ " ON DUPLICATE KEY UPDATE PaidDays = ?,Gross = ?,Basic = ?,HRA = ?,"
			+ "Conveyance = ?,Special = ?,Medic = ?,Others = ?,INCENTIVE = ?,"
			+ "PF = ?,ESI = ?,PTax = ?,LOP = ?,Earnings = ?,DECUCATION = ?,NetPay = ?";
		 */
		
		PreparedStatement preparedStatement = conn
				.prepareStatement(SalaryTableSQLConstants.insertOrUpdatePaidSalaryInfo);
		preparedStatement.setString(1, empId);
		preparedStatement.setString(2, month);
		preparedStatement.setString(3, year);
		preparedStatement.setString(4, salaryStracture.getEmployee().getEmpName());
		preparedStatement.setString(5, salaryStracture.getEmployee().getCompany().getCompanyID());
		preparedStatement.setString(6, salaryStracture.getEmployee().getDepertment().getCompanyCode());
		preparedStatement.setString(7, salaryStracture.getEmployee().getUnit());
		preparedStatement.setString(8, salaryStracture.getEmployee().getDesignation().getCode());
		preparedStatement.setString(9, salaryStracture.getEsiNumber());
		preparedStatement.setString(10, salaryStracture.getPfNumber());
		preparedStatement.setString(11, salaryStracture.getMaxDays());
		preparedStatement.setString(12, salaryStracture.getPaidDays());
		preparedStatement.setString(13, salaryStracture.getGrossSalary());
		preparedStatement.setString(14, salaryStracture.getBasic());
		preparedStatement.setString(15, salaryStracture.getHra());
		preparedStatement.setString(16, salaryStracture.getConveyance());
		preparedStatement.setString(17, salaryStracture.getSpecialAllowance());
		preparedStatement.setString(18, salaryStracture.getMedicalAllowance());
		preparedStatement.setString(19, salaryStracture.getOthers());
		preparedStatement.setString(20, salaryStracture.getIncentive());
		preparedStatement.setString(21, salaryStracture.getPfAmt());
		preparedStatement.setString(22, salaryStracture.getEsiNumber());
		preparedStatement.setString(23, salaryStracture.getPtAmount());
		preparedStatement.setString(24, salaryStracture.getLop());
		preparedStatement.setString(25, salaryStracture.getTotalEarnigs());
		preparedStatement.setString(26, salaryStracture.getTotalDesctions());
		preparedStatement.setString(27, salaryStracture.getNetPay());
		
		
		preparedStatement.setString(28, salaryStracture.getPaidDays());
		preparedStatement.setString(29, salaryStracture.getGrossSalary());
		preparedStatement.setString(30, salaryStracture.getBasic());
		preparedStatement.setString(31, salaryStracture.getHra());
		preparedStatement.setString(32, salaryStracture.getConveyance());
		preparedStatement.setString(33, salaryStracture.getSpecialAllowance());
		preparedStatement.setString(34, salaryStracture.getMedicalAllowance());
		preparedStatement.setString(35, salaryStracture.getOthers());
		preparedStatement.setString(36, salaryStracture.getIncentive());
		preparedStatement.setString(37, salaryStracture.getPfAmt());
		preparedStatement.setString(38, salaryStracture.getEsiNumber());
		preparedStatement.setString(39, salaryStracture.getPtAmount());
		preparedStatement.setString(40, salaryStracture.getLop());
		preparedStatement.setString(41, salaryStracture.getTotalEarnigs());
		preparedStatement.setString(42, salaryStracture.getTotalDesctions());
		preparedStatement.setString(43, salaryStracture.getNetPay());
		try{
			PayRollUtility.debug("preparedStatement : "+preparedStatement);
			preparedStatement.executeUpdate();
			
			
		}catch(Exception exception){
			
			throw new  BusinessException("Error while updating paid salary info for employee :  "+empId);
		}
		
		PayRollUtility.debug("Exiting method insertOrUpdatePaidSalaryInfo for emp"+empId);
	}
	
	public void recoverAmount(String empId,String rowNum,double amount) throws BusinessException{
		try{
			PreparedStatement preparedStatement = conn.prepareStatement(SalaryTableSQLConstants.updateSalaryAdvance);
			preparedStatement.setDouble(1, amount);
			preparedStatement.setString(2, empId);
			preparedStatement.setString(3, rowNum);

			preparedStatement.executeUpdate();

		}catch(Exception e){

			throw new BusinessException("Exception while updating salary advance for employee : "+empId);
		}
	}

}
